package com.douban.crawl.db;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class BaseDao {
    /**
     * 查询列表
     * @param clazz
     * @return
     */
    public ArrayList getList(Class clazz) {
        ArrayList arrayList = new ArrayList();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
                arrayList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

    /**
     * 列表查询（带limit）
     * @param clazz
     * @param queryMap
     * @return
     */
    public ArrayList getList(Class clazz, Integer limitSize) {
        ArrayList arrayList = new ArrayList();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            sql.append(" limit ").append(limitSize);
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
                arrayList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

    /**
     * 条件查询
     * @param clazz
     * @param queryMap
     * @return
     */
    public ArrayList getList(Class clazz, Map<String, String> queryMap) {
        ArrayList arrayList = new ArrayList();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            if (queryMap != null && queryMap.size() > 0) {
                sql.append(" where 1 = 1 ");
                Iterator<Map.Entry<String, String>> iterator = queryMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry<String, String> entry = iterator.next();
                    sql.append(" and ").append(entry.getKey()).append(" = '").append(entry.getValue()).append("' ");
                }
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
                arrayList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

    /**
     * 条件查询（带limit）
     * @param clazz
     * @param queryMap
     * @return
     */
    public ArrayList getList(Class clazz, Map<String, String> queryMap, Integer limitSize) {
        ArrayList arrayList = new ArrayList();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            if (queryMap != null && queryMap.size() > 0) {
                sql.append(" where 1 = 1 ");
                Iterator<Map.Entry<String, String>> iterator = queryMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry<String, String> entry = iterator.next();
                    sql.append(" and ").append(entry.getKey()).append(" = '").append(entry.getValue()).append("' ");
                }
            }
            sql.append(" limit ").append(limitSize);
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
                arrayList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

    /**
     * 查询单个对象
     * @param clazz
     * @param id
     * @return
     */
    public Object getOne(Class clazz, int id) {
        Object obj = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("select ");
            for (int i = 0; i < colList.size(); i++) {
                sql.append(colList.get(i));
                if (i != colList.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" from ").append(clazz.getSimpleName());
            sql.append(" where ").append(colList.get(0)).append(" = ? ");
            ps = conn.prepareStatement(sql.toString());
            ps.setObject(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                obj = clazz.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    if (colList.contains(fields[i].getName())) {
                        fields[i].setAccessible(true);
                        fields[i].set(obj, rs.getObject(fields[i].getName()));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return obj;
    }

    /**
     * 通用插入方法
     * @param obj
     * @return
     */
    public int insert(Object obj) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Class clazz = obj.getClass();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("insert into ");
            sql.append(clazz.getSimpleName()).append(" (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(fields[i].getName());
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(") values (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(" ?");
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(")");
            ps = conn.prepareStatement(sql.toString());
            int j = 0;
            for (int i = 0; i < fields.length; i++) {
                j = i + 1;
                if (colList.contains(fields[i].getName())) {
                    fields[i].setAccessible(true);
                    // 这里注意，占位符从1开始，不是从0开始
                    ps.setObject(j, fields[i].get(obj));
                }
            }
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

    /**
     * 获取主键值的插入方法
     * @param obj
     * @return
     */
    public int insertGetGenerateKey(Object obj) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            Class clazz = obj.getClass();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("insert into ");
            sql.append(clazz.getSimpleName()).append(" (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(fields[i].getName());
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(") values (");
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(" ?");
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(")");
            ps = conn.prepareStatement(sql.toString());
            int j = 0;
            for (int i = 0; i < fields.length; i++) {
                j = i + 1;
                if (colList.contains(fields[i].getName())) {
                    fields[i].setAccessible(true);
                    // 这里注意，占位符从1开始，不是从0开始
                    ps.setObject(j, fields[i].get(obj));
                }
            }
            res = ps.executeUpdate();
            if (res > 0) {
                rs =  ps.getGeneratedKeys();
                if (rs.next()) {
                    res = rs.getInt(1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

    /**
     * 通用更新方法
     * @param obj
     * @return
     */
    public int update(Object obj) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Class clazz = obj.getClass();
            Field [] fields = clazz.getDeclaredFields();
            List<String> colList = getAllColumns(clazz.getSimpleName());
            StringBuilder sql = new StringBuilder("update ");
            sql.append(clazz.getSimpleName());
            sql.append(" set ");
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                sql.append(fields[i].getName()).append(" = ? ");
                if (i != fields.length - 1) {
                    sql.append(", ");
                }
            }
            sql.append(" where ");
            sql.append(fields[0].getName()).append(" = ?");
            ps = conn.prepareStatement(sql.toString());
            for (int i = 1; i < fields.length; i++) {
                if (colList.contains(fields[i].getName())) {
                    fields[i].setAccessible(true);
                    // 这里注意，占位符从1开始，不是从0开始
                    ps.setObject(i, fields[i].get(obj));
                }
            }
            // id的位置
            int idIndex = 0;
            fields[idIndex].setAccessible(true);
            ps.setObject(fields.length, fields[idIndex].get(obj));
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }

    /**
     * 通用删除方法
     * @param clazz
     * @param id
     * @return
     */
    public int delete(Class clazz, int id) {
        int res = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConn();
            Field [] fields = clazz.getDeclaredFields();
            int idIndex = 0;
            int paramIndex = 1;
            StringBuilder sql = new StringBuilder("delete from ");
            sql.append(clazz.getSimpleName()).append(" where ");
            sql.append(fields[idIndex].getName()).append(" = ? ");
            ps = conn.prepareStatement(sql.toString());
            ps.setObject(paramIndex, id);
            res = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps);
        }
        return res;
    }



    /**
     * 获取所有列名
     * @param tableName
     * @return
     */
    public ArrayList<String> getAllColumn(String tableName) {
        ArrayList<String> arrayList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 只将列名查询出来，不查询数据
        String sql = "select * from " + tableName + " where 1 = 2";
        try {
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            // 列名从1开始取
            for (int i = 1; i <= count; i++) {
                String columnName = metaData.getColumnName(i);
                arrayList.add(columnName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }
        return arrayList;
    }

    public List<String> getAllColumns(String tableName) {
        ArrayList<String> arrayList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            // 获取数据库的元数据对象
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getColumns(null, "%", tableName, "%");
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
//                String columnType = rs.getString("TYPE_NAME");
//                int dataSize = rs.getInt("COLUMN_SIZE");
//                int digits = rs.getInt("DECIMAL_DIGITS");
//                int nullable = rs.getInt("NULLABLE");
                arrayList.add(columnName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn, ps, rs);
        }

        return arrayList;
    }
}

